package com.perfma.xtest.tool;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import javax.servlet.http.HttpServletResponse;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * 生成合并后的报告的excel
 *
 * @author: junhui.si
 * @date: 2020-10-09 14:59
 **/
public class ExcelWriteDemo3 {

    /**
     * 标题占用行数
     */
    private static final int TITLE_ROW_COUNT = 2;

    /**
     * 表格之间的空行
     */
    private static final int TABLE_INTERVAL_ROW_COUNT = 5;

    public static void main(String[] args) {
        String data = "{\"extParams\":{},\"object\":[{\"bizData\":{\"chartDataList\":[{\"barDataMap\":{\"集群管理列表\":\"42.25\",\"探针管理-trace-gateway-baili\":\"11.04\",\"探针详情\":\"6.83\",\"查看告警规则\":\"4.62\",\"禁用启用-trace-gateway-baili\":\"59.17\",\"查看告警列表\":\"30.5\"},\"chartName\":\"事务平均响应时间\",\"chartType\":\"WBAR\",\"xName\":\"平均响应时间(ms)\",\"yName\":\"事务名\"}],\"dataType\":\"BIZ\",\"tableDataList\":[{\"dataList\":[{\"successTps\":[\"99.66\",\"39.83\",\"59.75\",\"0.06\",\"0.06\",\"0\"],\"failCount\":[0,0,0,0,0,0],\"successPercent\":[\"100\"],\"allUsers\":[\"31\"],\"name\":[\"集群管理列表\",\"探针管理-trace-gateway-baili\",\"探针详情\",\"查看告警规则\",\"禁用启用-trace-gateway-baili\",\"查看告警列表\"],\"successCount\":[59997,24020,36031,40,40,4],\"ninetyResponseTime\":[\"66\",\"19\",\"10\",\"6\",\"19\",\"90\"],\"totalAvgResponseTime\":[\"42.25\",\"11.04\",\"6.83\",\"4.62\",\"59.17\",\"30.5\"]},{\"successTps\":[\"99.66\",\"39.83\",\"59.75\",\"0.06\",\"0.06\",\"0\"],\"failCount\":[0,0,0,0,0,0],\"successPercent\":[\"100\"],\"allUsers\":[\"33\"],\"name\":[\"集群管理列表\",\"探针管理-trace-gateway-baili\",\"探针详情\",\"查看告警规则\",\"禁用启用-trace-gateway-baili\",\"查看告警列表\"],\"successCount\":[59997,24020,36031,40,40,4],\"ninetyResponseTime\":[\"66\",\"19\",\"10\",\"6\",\"19\",\"90\"],\"totalAvgResponseTime\":[\"42.25\",\"11.04\",\"6.83\",\"4.62\",\"59.17\",\"30.5\"]}],\"tableName\":\"基准数据汇总\",\"titleMap\":{\"name\":\"事务名称\",\"allUsers\":\"总并发用户数\",\"successCount\":\"成功数\",\"failCount\":\"失败数\",\"successPercent\":\"成功率(%)\",\"successTps\":\"成功tps(笔/秒)\",\"totalAvgResponseTime\":\"平均响应时间(ms)\",\"ninetyResponseTime\":\"90%响应时间(ms)\"}},{\"dataList\":[{\"successTps\":[\"99.66\",\"39.83\",\"59.75\",\"0.06\",\"0.06\",\"0\"],\"failCount\":[0,0,0,0,0,0],\"successPercent\":[\"100\"],\"allUsers\":[\"31\"],\"name\":[\"集群管理列表\",\"探针管理-trace-gateway-baili\",\"探针详情\",\"查看告警规则\",\"禁用启用-trace-gateway-baili\",\"查看告警列表\"],\"successCount\":[59997,24020,36031,40,40,4],\"ninetyResponseTime\":[\"66\",\"19\",\"10\",\"6\",\"19\",\"90\"],\"totalAvgResponseTime\":[\"42.25\",\"11.04\",\"6.83\",\"4.62\",\"59.17\",\"30.5\"]},{\"successTps\":[\"99.66\",\"39.83\",\"59.75\",\"0.06\",\"0.06\",\"0\"],\"failCount\":[0,0,0,0,0,0],\"successPercent\":[\"100\"],\"allUsers\":[\"33\"],\"name\":[\"集群管理列表\",\"探针管理-trace-gateway-baili\",\"探针详情\",\"查看告警规则\",\"禁用启用-trace-gateway-baili\",\"查看告警列表\"],\"successCount\":[59997,24020,36031,40,40,4],\"ninetyResponseTime\":[\"66\",\"19\",\"10\",\"6\",\"19\",\"90\"],\"totalAvgResponseTime\":[\"42.25\",\"11.04\",\"6.83\",\"4.62\",\"59.17\",\"30.5\"]}],\"tableName\":\"基准数据汇总\",\"titleMap\":{\"name\":\"事务名称\",\"allUsers\":\"总并发用户数\",\"successCount\":\"成功数\",\"failCount\":\"失败数\",\"successPercent\":\"成功率(%)\",\"successTps\":\"成功tps(笔/秒)\",\"totalAvgResponseTime\":\"平均响应时间(ms)\",\"ninetyResponseTime\":\"90%响应时间(ms)\"}}]},\"name\":\"基准场景\",\"planGoalType\":\"BASELINE\",\"reportIdList\":[515562453820506100]},{\"bizData\":{\"chartDataList\":[{\"barDataMap\":{\"集群管理列表\":\"42.25\",\"探针管理-trace-gateway-baili\":\"11.04\",\"探针详情\":\"6.83\",\"查看告警规则\":\"4.62\",\"禁用启用-trace-gateway-baili\":\"59.17\",\"查看告警列表\":\"30.5\"},\"chartName\":\"事务平均响应时间\",\"chartType\":\"WBAR\",\"xName\":\"平均响应时间(ms)\",\"yName\":\"事务名\"}],\"dataType\":\"BIZ\",\"tableDataList\":[{\"dataList\":[{\"successTps\":[\"99.66\",\"39.83\",\"59.75\",\"0.06\",\"0.06\",\"0\"],\"failCount\":[0,0,0,0,0,0],\"successPercent\":[\"100\"],\"allUsers\":[\"31\"],\"name\":[\"集群管理列表\",\"探针管理-trace-gateway-baili\",\"探针详情\",\"查看告警规则\",\"禁用启用-trace-gateway-baili\",\"查看告警列表\"],\"successCount\":[59997,24020,36031,40,40,4],\"ninetyResponseTime\":[\"66\",\"19\",\"10\",\"6\",\"19\",\"90\"],\"totalAvgResponseTime\":[\"42.25\",\"11.04\",\"6.83\",\"4.62\",\"59.17\",\"30.5\"]},{\"successTps\":[\"99.66\",\"39.83\",\"59.75\",\"0.06\",\"0.06\",\"0\"],\"failCount\":[0,0,0,0,0,0],\"successPercent\":[\"100\"],\"allUsers\":[\"33\"],\"name\":[\"集群管理列表\",\"探针管理-trace-gateway-baili\",\"探针详情\",\"查看告警规则\",\"禁用启用-trace-gateway-baili\",\"查看告警列表\"],\"successCount\":[59997,24020,36031,40,40,4],\"ninetyResponseTime\":[\"66\",\"19\",\"10\",\"6\",\"19\",\"90\"],\"totalAvgResponseTime\":[\"42.25\",\"11.04\",\"6.83\",\"4.62\",\"59.17\",\"30.5\"]}],\"tableName\":\"基准数据汇总\",\"titleMap\":{\"name\":\"事务名称\",\"allUsers\":\"总并发用户数\",\"successCount\":\"成功数\",\"failCount\":\"失败数\",\"successPercent\":\"成功率(%)\",\"successTps\":\"成功tps(笔/秒)\",\"totalAvgResponseTime\":\"平均响应时间(ms)\",\"ninetyResponseTime\":\"90%响应时间(ms)\"}},{\"dataList\":[{\"successTps\":[\"99.66\",\"39.83\",\"59.75\",\"0.06\",\"0.06\",\"0\"],\"failCount\":[0,0,0,0,0,0],\"successPercent\":[\"100\"],\"allUsers\":[\"31\"],\"name\":[\"集群管理列表\",\"探针管理-trace-gateway-baili\",\"探针详情\",\"查看告警规则\",\"禁用启用-trace-gateway-baili\",\"查看告警列表\"],\"successCount\":[59997,24020,36031,40,40,4],\"ninetyResponseTime\":[\"66\",\"19\",\"10\",\"6\",\"19\",\"90\"],\"totalAvgResponseTime\":[\"42.25\",\"11.04\",\"6.83\",\"4.62\",\"59.17\",\"30.5\"]},{\"successTps\":[\"99.66\",\"39.83\",\"59.75\"],\"failCount\":[0,0,0,0,0,0],\"successPercent\":[\"100\"],\"allUsers\":[\"33\"],\"name\":[\"集群管理列表\",\"探针管理-trace-gateway-baili\",\"探针详情\",\"查看告警规则\",\"禁用启用-trace-gateway-baili\",\"查看告警列表\"],\"successCount\":[59997,24020,36031,40,40,4],\"ninetyResponseTime\":[\"66\",\"19\",\"10\",\"6\",\"19\",\"90\"],\"totalAvgResponseTime\":[\"42.25\",\"11.04\",\"6.83\",\"4.62\",\"59.17\",\"30.5\"]}],\"tableName\":\"基准数据汇总\",\"titleMap\":{\"name\":\"事务名称\",\"allUsers\":\"总并发用户数\",\"successCount\":\"成功数\",\"failCount\":\"失败数\",\"successPercent\":\"成功率(%)\",\"successTps\":\"成功tps(笔/秒)\",\"totalAvgResponseTime\":\"平均响应时间(ms)\",\"ninetyResponseTime\":\"90%响应时间(ms)\"}}]},\"name\":\"基准场景2\",\"planGoalType\":\"BASELINE\",\"reportIdList\":[515562453820506100]}],\"success\":true}";
        String fileName = TestFileUtil.getPath() + "excel.xlsx";
        ExcelWriter excelWriter = null;
        try {
            JSONObject root = JSON.parseObject(data);
            JSONArray objectArrayJson = root.getJSONArray("object");

            excelWriter = EasyExcel.write(fileName)
                    // 行宽自适应
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    // 样式
                    .registerWriteHandler(getWriteHandler())
                    .build();

            int sheetIndex = 0;
            for (Object o : objectArrayJson) {
                JSONObject sheetDataJson = (JSONObject) o;
                // 根据数据生成sheet
                generateSheet(excelWriter, sheetDataJson, sheetIndex);
                sheetIndex++;
            }
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    /**
     * 生成sheet
     *
     * @param excelWriter   excel操作对象
     * @param sheetDataJson sheet相关的json数据
     * @param sheetIndex    sheet索引
     */
    private static void generateSheet(ExcelWriter excelWriter, JSONObject sheetDataJson, int sheetIndex) {
        JSONObject bizDataJson = sheetDataJson.getJSONObject("bizData");
        JSONArray tableDataListJson = bizDataJson.getJSONArray("tableDataList");

        String sheetName = sheetDataJson.getString("name");
        WriteSheet writeSheet = EasyExcel.writerSheet(sheetIndex, sheetName).build();

        // tableDataList中一个元素对应一个表格（table）
        int tableRowOffset = 0, tableColumnOffset = 0, tableIndex = 0;
        for (Object o : tableDataListJson) {
            JSONObject tableDataJson = (JSONObject) o;

            int tableRowCount = generateTable(excelWriter, writeSheet, tableDataJson, tableRowOffset, tableColumnOffset, tableIndex);

            // 计算行位移
            tableRowOffset = TABLE_INTERVAL_ROW_COUNT + tableRowCount;
            tableIndex++;
        }
    }

    /**
     * 生成表格
     *
     * @param excelWriter       excel操作对象
     * @param writeSheet        sheet操作对象
     * @param tableDataJson     table相关的json数据
     * @param tableRowOffset    表格行位移
     * @param tableColumnOffset 表格列位移
     * @param tableIndex        表格索引
     * @return 表格占用行数
     */
    private static int generateTable(ExcelWriter excelWriter, WriteSheet writeSheet, JSONObject tableDataJson, int tableRowOffset, int tableColumnOffset, int tableIndex) {
        // 表格名
        String tableName = tableDataJson.getString("tableName");
        JSONObject nameJson = tableDataJson.getJSONObject("titleMap");
        JSONArray dataListJson = tableDataJson.getJSONArray("dataList");

        // 表头。顺序问题 todo sjh
        Map<String, String> headMapTemp = JSONObject.toJavaObject(nameJson, Map.class);
        // key:字段名，value:表头中文名
        LinkedHashMap<String, String> headMap = Maps.newLinkedHashMap(headMapTemp);
        // key:字段名，value:行序号（相对）
        Map<String, Integer> titleOrderMap = getTitleOrderMap(headMap);

        WriteTable writeTable = EasyExcel.writerTable(tableIndex).needHead(Boolean.TRUE).build();
        // 设置表头
        writeTable.setHead(getHead(tableName, Lists.newArrayList(headMap.values())));
        // 设置合并单元格的规则
        writeTable.setCustomWriteHandlerList(getMergeWriteHandlerList(titleOrderMap, dataListJson, tableRowOffset, tableColumnOffset));
        if (tableIndex > 0) {
            // 每个表格之间空出 {@link TABLE_INTERVAL_ROW_COUNT} 行
            writeTable.setRelativeHeadRowIndex(TABLE_INTERVAL_ROW_COUNT);
        }

        // 写入数据
        List<List<Object>> dataList = dataList(headMap, dataListJson);
        excelWriter.write(dataList, writeSheet, writeTable);

        return dataList.size() + TITLE_ROW_COUNT;
    }

    /**
     * 表格样式
     *
     * @return 表格样式
     */
    private static WriteHandler getWriteHandler() {
        // 表头的样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 16);
        headWriteFont.setFontName("宋体");
        headWriteCellStyle.setWriteFont(headWriteFont);

        // 内容的样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 14);
        contentWriteFont.setFontName("宋体");
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    /**
     * 获取表格的表头
     *
     * @param tableName 表格名
     * @param nameList  表头列表
     * @return 表格的表头
     */
    private static List<List<String>> getHead(String tableName, List<String> nameList) {
        List<List<String>> head = Lists.newArrayList();
        for (String name : nameList) {
            head.add(Lists.newArrayList(tableName, name));
        }
        return head;
    }

    /**
     * 合并单元格的规则
     * 逻辑：应用{@link OnceRelativeMergeStrategy}，计算出需要合并的单元格的坐标范围
     *
     * @param titleOrderMap     表头map
     * @param dataListJson      数据
     * @param tableRowOffset    表格行位移
     * @param tableColumnOffset 表格列位移
     * @return 合并单元格的规则
     */
    private static List<WriteHandler> getMergeWriteHandlerList(Map<String, Integer> titleOrderMap, JSONArray dataListJson, int tableRowOffset, int tableColumnOffset) {
        List<WriteHandler> writeHandlers = Lists.newArrayList();
        // 行相对位移（相对于自身表格的位移）
        int relativeRowOffset = 0;
        for (Object o : dataListJson) {
            JSONObject itemJson = (JSONObject) o;

            // 数据行数
            int rowCount = getRowCount(itemJson);
            if (rowCount > 1) {
                // 需要合并单元格的列序号
                List<Integer> needMergeColumnList = getNeedMergeColumn(titleOrderMap, itemJson);

                for (Integer needMergeColumn : needMergeColumnList) {
                    int lastRowIndex = relativeRowOffset + rowCount - 1;
                    int rowOffset = tableRowOffset + TITLE_ROW_COUNT;

                    // 合并一次的规则
                    writeHandlers.add(new OnceRelativeMergeStrategy(relativeRowOffset, lastRowIndex,
                            needMergeColumn, needMergeColumn,
                            rowOffset, tableColumnOffset));
                }
            }

            relativeRowOffset += rowCount;
        }
        return writeHandlers;
    }

    /**
     * 获取表头map
     * key:字段名，value:行序号（相对）
     *
     * @param headMap 表头map
     * @return 表头map
     */
    private static Map<String, Integer> getTitleOrderMap(LinkedHashMap<String, String> headMap) {
        int order = 0;
        Map<String, Integer> titleOrderMap = Maps.newHashMap();
        for (String key : headMap.keySet()) {
            titleOrderMap.put(key, order++);
        }
        return titleOrderMap;
    }

    /**
     * 获取这一数据块的行数
     *
     * @param itemJson dataList中的元素
     * @return 行数
     */
    private static int getRowCount(JSONObject itemJson) {
        int maxRowCount = 0;
        for (Object o : itemJson.getInnerMap().values()) {
            maxRowCount = Math.max(maxRowCount, ((JSONArray) o).size());
        }
        return maxRowCount;
    }

    /**
     * 获取需要合并单元的列序号列表
     * 若dataList[n]中的某个字段只有一个值，其他字段不止一个值，则根据数据格式协定，该字段即为需要合并单元格的字段
     *
     * @param titleOrderMap 表头map
     * @param itemJson      数据
     * @return 需要合并单元的列序号列表
     */
    private static List<Integer> getNeedMergeColumn(Map<String, Integer> titleOrderMap, JSONObject itemJson) {
        List<Integer> needMergeColumn = Lists.newArrayList();

        for (Map.Entry<String, Object> entry : itemJson.getInnerMap().entrySet()) {
            JSONArray array = (JSONArray) entry.getValue();
            if (array.size() == 1) {
                needMergeColumn.add(titleOrderMap.get(entry.getKey()));
            }
        }
        return needMergeColumn;
    }

    /**
     * 获取数据行
     *
     * @param headMap      表头
     * @param dataListJson 数据
     * @return 数据行
     */
    private static List<List<Object>> dataList(LinkedHashMap<String, String> headMap, JSONArray dataListJson) {
        List<List<Object>> resultList = Lists.newArrayList();
        List<Object> result;

        for (Object o : dataListJson) {
            JSONObject itemJson = (JSONObject) o;

            // 表总数据行
            int rowCount = getRowCount(itemJson);
            for (int index = 0; index < rowCount; index++) {
                result = Lists.newArrayList();

                for (Map.Entry<String, String> entry : headMap.entrySet()) {
                    // 表头对应的字段名
                    String key = entry.getKey();
                    JSONArray valueListJson = itemJson.getJSONArray(key);

                    if (valueListJson == null) {
                        result.add(null);
                        continue;
                    }

                    // 根据数据格式协定，需要合并单元格的字段的数组中只有一个值，因为值都一样
                    if (valueListJson.size() == 1) {
                        result.add(valueListJson.get(0));
                        continue;
                    }

                    // 若数据不够，则填充null。一般不会出现，若出现，则是传入的数据问题。
                    if (valueListJson.size() - 1 < index) {
                        result.add(null);
                        continue;
                    }

                    result.add(valueListJson.get(index));
                }
                resultList.add(result);
            }
        }
        return resultList;
    }


}
